package c4.billing.cyclemanager;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;


/**
 * This class is used to "print" a customers invoice (it will create a .txt file
 * with information related to each person's account).
 */
public class InvoicePrinting {
	
    public InvoicePrinting(){
	
		try {
			Statement stmtAccounts, stmtPhones, stmtCharges, stmtRates;
			ResultSet rsAccounts, rsPhones, rsCharges, rsRates;
			String query, accountNum, phone, type;
			double calls, voicemail, threeWay, callerId, taxes, duration;
			double lineCharges, lineTaxes, bill;
			String output = "";
			
			/*
			 * Initialize rates and charges
			 */
			calls = 0;
			voicemail = 0;
			threeWay = 0;
			callerId = 0;
			taxes = 0;
			lineCharges = 0;
			lineTaxes = 0;

			/*
			 * Setup database objects
			 */
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			
		    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/corporate?" + 
		                                   "user=c4_user&password=p@ssw0rd");
		    /*
		     * Setup all the statement objects;
		     */
			stmtAccounts = conn.createStatement();
			stmtPhones = conn.createStatement();
			stmtCharges = conn.createStatement();
			stmtRates = conn.createStatement();
			
			/**
			 * Set the rates
			 */
			query = "SELECT * FROM rates;";
			
			rsRates = stmtRates.executeQuery(query);
			
			while(rsRates.next()) {
				type = rsRates.getString("service_name");
				
				if(type.equals("call"))
					calls = rsRates.getDouble("dollars");
				else if(type.equals("caller_id"))
					callerId = rsRates.getDouble("dollars");
				else if(type.equals("taxes"))
					taxes = rsRates.getDouble("dollars");
				else if(type.equals("three_way"))
					threeWay = rsRates.getDouble("dollars");
				else
					voicemail = rsRates.getDouble("dollars");
			}

			/*
			 * Loop through Accounts in account_information table
			 */
			query =	"SELECT * FROM account_information;";
			rsAccounts = stmtAccounts.executeQuery(query);
			
			while(rsAccounts.next()) {
				
				output += "\n\n\t\t\tCUSTOMER INVOICE\n";
				output += "\t\t\t----------------\n\n";
				
				output += "Account Number:\t " + rsAccounts.getString("account_number") + "\n";
				output += "Name:\t\t\t " + rsAccounts.getString("name") + "\n";
				output += "Address:\t\t " + rsAccounts.getString("street") + "\n";
				output += "\t\t\t " + rsAccounts.getString("city") + ", " + rsAccounts.getString("state");
				output += " " + rsAccounts.getString("zip") + "\n\n";
				
				/*
				 * Display standard charges
				 */
				output += "Standard fees\n";
				output += "Calls:\t$" + calls + " (per minute)\n";
				output += "Voicemail:\t$" + voicemail + " (per month)\n";
				output += "Caller ID:\t$" + callerId + " (per month)\n";
				output += "Three Way:\t$" + threeWay + " (per month)\n";
				output += "\n";
				
				/*
				 * Determine what services they have
				 */
				output += "Monthly services on account\n";
				output += "Voicemail:\t";
				if(rsAccounts.getString(Constants.VOICEMAIL).equals("Y"))
					output += "Y\n";
				else
					output += "N\n";
				
				output += "Caller ID:\t";
				
				if(rsAccounts.getString(Constants.CALLER_ID).equals("Y"))
					output += "Y\n";
				else
					output += "N\n";
				
				output += "Three Way:\t";

				if(rsAccounts.getString(Constants.THREE_WAY).equals("Y"))
					output += "Y\n";
				else
					output += "N\n";
				
				output += "\n";
				
				/*
				 * Loop through the charges per phone
				 */
				accountNum = rsAccounts.getString(Constants.ACCOUNT_NUMBER);
				
				query = "SELECT * FROM charges " +
						"WHERE account_number=" + accountNum + ";";
				
				rsCharges = stmtCharges.executeQuery(query);
				
				output += "Overview of charges per line\n";
				
				while(rsCharges.next()) {
					output += "Line: " + rsCharges.getString("phone_number") + " - $";
					output += (rsCharges.getDouble("charges") + rsCharges.getDouble("taxes"));
					output += "\n";
				}
				
				/*
				 * Loop through the phone lines associated with this account number
				 */

				output += "\nDetailed line information\n";
				
				query = "SELECT phone_number FROM phone_number_listing " +
						"WHERE account_number=" + accountNum + ";";
		
				rsPhones = stmtPhones.executeQuery(query);
				
				while(rsPhones.next()) {
					phone = rsPhones.getString(Constants.PHONE_NUMBER);
					
					output += "Line: " + phone + "\n";
					
					/*
					 * Loop through the calls associated with this phone number
					 */
					query = "SELECT * " +
							"FROM call_log " +
							"WHERE phone_number=" + phone + ";";
					
					rsCharges = stmtCharges.executeQuery(query);
					
					/*
					 * Show the calls for this line
					 */
					while(rsCharges.next()) {
						output += "\t";
						output += rsCharges.getString("date_made") + " - ";
						output += rsCharges.getString("other_number") + " - ";
						output += rsCharges.getString("duration") + "\n";
					}
				}
				
				/*
				 * Display total due for this account
				 */
				query = "SELECT bill FROM bills WHERE account_number=" + accountNum + ";";
				
				rsCharges = stmtCharges.executeQuery(query);
				
				output += "\nTotal due: $";
				
				if(rsCharges.next())
					output += rsCharges.getString("bill");
			}
			//System.out.println(output);
			write(output);
	    }
		catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
	}
    
    /**
     * Returns a rounded double
     * 
     * @param d				double to round
     * @param decimalPlace	Number of decimal places to round the double
     * @return Rounded double
     */
    public static double round(double d, int decimalPlace){
        BigDecimal bd = new BigDecimal(Double.toString(d));
        bd = bd.setScale(decimalPlace,BigDecimal.ROUND_HALF_UP);
        return bd.doubleValue();
      }
    
    /**
     * Writes to a file named invoices.txt which is placed on the 
     * desktop. This method is PLATFORM DEPENDENT and will only
     * work on Windows configurations.
     * 
     * @param s	String that should be written to the file 
     */
    public void write(String s) {
    	File outFile = null;
    	FileWriter out = null;
    	String desktopDir = null;
    	
    	try {
    		
    		desktopDir = System.getProperty("user.home") + "\\Desktop";
	    	outFile = new File(desktopDir + "\\c4_invoices.txt");
	        out = new FileWriter(outFile);
	        out.write(s);
	        out.close();
    	}
    	catch (Exception e) {
    		e.printStackTrace(System.err);
    	}
    }
}


